﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;

namespace OF.DB.DataAccess.Query
{
    public class SqlAndConditionBuilder
    {
        private const string ParameterNamePrefix = "@AGParam_";
        private int paramIndex = 0;
        private bool hasCondition = false;
        private StringBuilder sbCondition = new StringBuilder();

        private Dictionary<int, string> tableAliasMappingDict = null;
        private Dictionary<int, string> tableJoinTableDict = null;
        private HashSet<int> tableUsedSet = new HashSet<int>();
        private List<SqlParameter> sqlParamList = new List<SqlParameter>();
        private List<string> declareTables = new List<string>();

        public SqlAndConditionBuilder(Dictionary<int, string> tableAliasMappingDict, Dictionary<int, string> tableJoinTableDict)
        {
            this.tableAliasMappingDict = tableAliasMappingDict;
            this.tableJoinTableDict = tableJoinTableDict;
        }

        public string GetJoinConditions()
        {
            List<string> joinList = new List<string>();
            foreach (var tableInt in tableUsedSet)
            {
                if (tableJoinTableDict.ContainsKey(tableInt))
                {
                    joinList.Add(tableJoinTableDict[tableInt]);
                }
            }
            return string.Join(Environment.NewLine, joinList);
        }

        private string GetParameterName()
        {
            return string.Format("{0}{1}", ParameterNamePrefix, paramIndex++);
        }

        public SqlAndConditionBuilder AddCondition(string condition)
        {
            if (hasCondition)
            {
                sbCondition.Append(" AND ");
            }
            else
            {
                sbCondition.Append(" WHERE ");
                hasCondition = true;
            }
            sbCondition.Append(condition);
            return this;
        }

        private class AndConditionParamResult
        {
            public string tableAlias;
            public string paramName;
        }

        public string AssignNewParameter(SqlDbType valueType, object value)
        {
            string paramName = GetParameterName();
            SqlParameter param = new SqlParameter(paramName, valueType) { Value = value };
            sqlParamList.Add(param);
            return paramName;
        }

        private AndConditionParamResult AppendNewQueryParam(Enum table, SqlDbType valueType, object value)
        {
            string paramName = AssignNewParameter(valueType, value);
            if (hasCondition)
            {
                sbCondition.Append(" AND ");
            }
            else
            {
                sbCondition.Append(" WHERE ");
                hasCondition = true;
            }
            int tableInt = Convert.ToInt32(table);
            string tableAlias = tableAliasMappingDict[tableInt];
            if (!tableUsedSet.Contains(tableInt))
            {
                tableUsedSet.Add(tableInt);
            }
            return new AndConditionParamResult
            {
                paramName = paramName,
                tableAlias = tableAlias
            };
        }

        public object GetDBParamValue(object value)
        {
            if (value == null)
            {
                value = DBNull.Value;
            }
            return value;
        }

        public SqlAndConditionBuilder AddEqual(Enum table, string columnName, SqlDbType valueType, object value)
        {
            object dbValue = GetDBParamValue(value);
            AndConditionParamResult addParamResult = AppendNewQueryParam(table, valueType, dbValue);
            sbCondition.Append(string.Format("{0}.{1}={2}", addParamResult.tableAlias, columnName, addParamResult.paramName));
            return this;
        }

        private string GetXMLDBParamValue(List<int> idList)
        {
            string value = string.Format("<d>{0}</d>", string.Join(string.Empty, idList.Select(id => string.Format((string)"<i>{0}</i>", (object)id))));
            return value;
        }

        public string GetMemoryTableIdQuery(List<int> idList)
        {
            string xmlValue = GetXMLDBParamValue(idList);
            string paramName = AssignNewParameter(SqlDbType.Xml, xmlValue);
            string xmlQuery = string.Format("SELECT Id=T.c.value('.[1]', 'INT') FROM {0}.nodes('/d/i') as T(c)", paramName);
            string tableName = GetParameterName();
            string tableDefine = string.Format(@"DECLARE {0} TABLE(Id INT);INSERT INTO {0}(Id) {1};", tableName, xmlQuery);
            declareTables.Add(tableDefine);
            return tableName;
        }

        public string GetDeclareTables()
        {
            return string.Join(Environment.NewLine, declareTables) + Environment.NewLine;
        }

        public string GetLikeParamValue(string value)
        {
            if (string.IsNullOrEmpty(value))
            {
                value = "%%";
            }
            else
            {
                value = string.Format("%{0}%", value);
            }
            return value;
        }

        public SqlAndConditionBuilder AddContains(Enum table, string columnName, SqlDbType valueType, string value)
        {
            string likeValue = GetLikeParamValue(value);
            AndConditionParamResult addParamResult = AppendNewQueryParam(table, valueType, likeValue);
            sbCondition.Append(string.Format("{0}.{1} LIKE {2} ESCAPE N'~'", addParamResult.tableAlias, columnName, addParamResult.paramName));
            return this;
        }

        public string GetConditionSql()
        {
            return sbCondition.ToString();
        }

        public SqlParameter[] GetSqlParams()
        {
            if (sqlParamList != null && sqlParamList.Count > 0)
            {
                return sqlParamList.ToArray();
            }
            else
            {
                return null;
            }
        }

        public SqlQueryContext Build(string sqlFormat)
        {
            string joinConditions = GetJoinConditions();
            string sql = string.Format(sqlFormat, joinConditions, GetConditionSql());
            return new SqlQueryContext
            {
                Sql = sql,
                Params = GetSqlParams(),
                DeclareVariables = GetDeclareTables()
            };
        }
    }
}
